package com.fsnbigdata.repository;
import com.fsnbigdata.domain.Vegetation;
import com.fsnbigdata.domain.dto.NamingPair;
import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.Query;
import org.springframework.roo.addon.layers.repository.jpa.annotations.RooJpaRepository;
import io.springlets.data.jpa.repository.DetachableJpaRepository;
import org.springframework.transaction.annotation.Transactional;

/**
 * = VegetationRepository
 *
 * TODO Auto-generated class documentation
 *
 */
@RooJpaRepository(entity = Vegetation.class)
@Transactional(readOnly = true)
public interface VegetationRepository extends DetachableJpaRepository<Vegetation, Long>, VegetationRepositoryCustom {

    List<NamingPair> findAllByInitial(String initial);

    List <Vegetation> findAllByCategory(String category);

    Page<Vegetation> findAll(Specification<Vegetation> spec, Pageable pageable);

    @Query(value = "select id,name,habitat_temperature,habitat_agrotype,habitat_altitude,habitat_precipitation from vegetation where if(?1 !='',name like concat('%',?1,'%'),1=1) AND if(?2 !='',category like concat('%',?2,'%'),1=1) AND if(?3 !='',id in(?4),1=1) ORDER BY ?#{#pageable}",
            countQuery = "select count(*) from vegetation where if(?1 !='',name like concat('%',?1,'%'),1=1) AND if(?2 !='',category like concat('%',?2,'%'),1=1) AND if(?3 !='',id in(?4),1=1)",nativeQuery = true)
    List<Object> queryAllByKey(String name, String category, String sign, List<Long> ids, Pageable pageable);

    @Query(value = "select vegetation_id from vegetation_distribution where if(?1 !='',city like concat('%',?1,'%'),1=1) or if(?1 !='',province like concat('%',?1,'%'),1=1) or if(?1 !='',county like concat('%',?1,'%'),1=1) GROUP BY vegetation_id",nativeQuery = true)
    List<Long> queryIds(String location);

    @Query(value = "select count(*) from vegetation where if(?1 !='',name like concat('%',?1,'%'),1=1) AND if(?2 !='',category like concat('%',?2,'%'),1=1) AND if(?3 !='',id in(?4),1=1)",nativeQuery = true)
    Long queryCountByKey(String name,String category,String sign, List<Long> ids);

    @Query(value = "select * from vegetation",nativeQuery = true)
    List<Object> getCount();
}
